mysql -p -u sent

use test;

CREATE TABLE IF NOT EXISTS `test` (
  `pk` int(11) NOT NULL,
  `a` int(11) NOT NULL,
  `b` binary(16) NOT NULL,
  `s` int(11) NOT NULL,
  PRIMARY KEY (`pk`),
  KEY `test_idx` (`a`),
  KEY `test_idx1` (`b`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;



delete from test;
INSERT INTO test VALUES (0, 0, 0x00000000000000000000000000000000, 0);
INSERT INTO test VALUES (1, 1, 0x00000000000000000000000000000001, 1);
INSERT INTO test VALUES (2, 0, 0x00000000000000000000000000000000, 0);
INSERT INTO test VALUES (3, 1, 0x00000000000000000000000000000001, 1);
INSERT INTO test VALUES (4, 2, 0x00000000000000000000000000000001, 2);
INSERT INTO test VALUES (5, 3, 0x00000000000000000000000000000001, 3);
INSERT INTO test VALUES (6, 4, 0x00000000000000000000000000000001, 4);
INSERT INTO test VALUES (7, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (8, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (9, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (10, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (11, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (12, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (13, 1, 0x00000000000000000000000000000000, 1);
INSERT INTO test VALUES (14, 1, 0x00000000000000000000000000000002, 1);

--test 1
begin;
select * from test where a>0 and b=0x00000000000000000000000000000000 for update;

begin;
select * from test where a>0 and b=0x00000000000000000000000000000001 for update;

--test 2
begin;
select * from test where a between 3 and 4 and b=0x00000000000000000000000000000001 for update;

begin;
select * from test where a between 0 and 1 and b=0x00000000000000000000000000000001 for update;

--test 3
begin;
select * from test where a=4 and b=0x00000000000000000000000000000001 for update;

begin;
select * from test where a=1 and b=0x00000000000000000000000000000001 for update;

--test 4
begin;
select * from test where a in (3, 4) and b=0x00000000000000000000000000000001 for update;

begin;
select * from test where a in (1,2) and b=0x00000000000000000000000000000001 for update;

--test 5
begin;
select * from test where a<0 and b=0x00000000000000000000000000000001 for update;

begin;
select * from test where a>0 and b=0x00000000000000000000000000000001 for update;

--test 6
begin;
select * from test where pk=1 for update;

begin;
select * from test where a=4 for update;

update test set a=4 where pk=1;

--test 7
begin;
select * from test where pk=1 for update;

begin;
select * from test where pk>1 for update;

--test 8
begin;
select * from test where a>2 and b=0x00000000000000000000000000000001 for update;

--update test set a=a+1 where a>2;

begin;
select * from test where a>0 and b=0x00000000000000000000000000000000 for update;

--test 9

begin;
select * from test where a>2 for update;

begin;
select * from test where a<=2 for update;

select * from test where a<2 for update;

--test 10
BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

UPDATE test SET a=1 WHERE a>0;

--test 11
BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

UPDATE test SET a=1 WHERE c=0;
UPDATE test SET a=1 WHERE a=0;

--test 11
BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

BEGIN;
SELECT a FROM test WHERE pk=0 FOR UPDATE;

SELECT a FROM test WHERE c=0 FOR UPDATE;

SELECT a FROM test WHERE a>0 FOR UPDATE;

--test 12
DROP TABLE table_pks;
begin;
CREATE TEMPORARY TABLE table_pks AS (select pk from test where a in (3, 4) and b=0x00000000000000000000000000000001);
select * from table_pks;
select * from test where pk in (select pk from table_pks) for update;

DROP TABLE table_pks;
begin;
CREATE TEMPORARY TABLE table_pks AS (select pk from test where a in (1, 2) and b=0x00000000000000000000000000000001);
select * from table_pks;
select * from test where pk in (select pk from table_pks) for update;

--test 13
begin;
select * from test where pk in (5,6) for update;

begin;
select * from test where pk in (1,3,4) for update;

--test 14
begin;
CREATE TEMPORARY TABLE IF NOT EXISTS table_pks (tpk INT NOT NULL);
TRUNCATE TABLE table_pks;
begin;
INSERT INTO table_pks select pk from test where a in (3, 4) and b=0x00000000000000000000000000000001;
select * from table_pks;
select * from test where pk in (select tpk from table_pks) for update;

begin;
CREATE TEMPORARY TABLE IF NOT EXISTS table_pks1 (tpk INT NOT NULL);
TRUNCATE TABLE table_pks1;
begin;
INSERT INTO table_pks1 select pk from test where a in (1, 2) and b=0x00000000000000000000000000000001;
select * from table_pks1;
select * from test where pk in (select tpk from table_pks1) for update;

--test 15
begin; select * from test where b=0x00000000000000000000000000000002 lock in share mode;

begin;
select * from test where a>0 and b=0x00000000000000000000000000000002 for update;

--test 16

begin; INSERT INTO t1 VALUES (0x00000000000000000000000000000002, 1) ON DUPLICATE KEY UPDATE s=1;

begin; INSERT INTO t1 VALUES (0x00000000000000000000000000000001, 1) ON DUPLICATE KEY UPDATE s=1;

begin; select * from t1 WHERE s=1 limit 1 for update;

--test 17

begin; select * from test WHERE pk=14 limit 1;

begin; UPDATE test SET s=2 WHERE pk=14;

select * from test WHERE pk=14 limit 1;
--SHOW ENGINE INNODB STATUS;

CREATE TEMPORARY TABLE table_pks ENGINE=MEMORY SELECT pk FROM test WHERE a>=1 AND a<=3;
UPDATE test SET a=0 WHERE pk IN (SELECT pk FROM table_pks);
DROP TEMPORARY TABLE IF EXISTS  table_pks;

